/*
Given a document tree path containing only folders, starting with the root, makes sure the folder path exists by
creating any folder in the path that does not.  If all goes well, the HierarchyKey of the end folder is returned.
Null is returned if the root does not exist (it won't ALTER  roots) or if the user does not have ALTER  permission
anywhere along the way. The parts of the path must be separated by the forward slash (/)
*/
CREATE PROC [dbo].[asi_DocumentEnsureFolder]
   @documentPath nvarchar(2000),
   @organizationKey uniqueidentifier,
   @userKey uniqueidentifier,
   @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- if this is empty, we assume the user is not logged in
   @rootHierarchyKey uniqueidentifier = null
AS
BEGIN
   DECLARE
      @parent uniqueidentifier,
      @documentRootName nvarchar(100),
      @documentName nvarchar(100),
      @ptr int,
      @newSort int,
      @documentKey uniqueidentifier,
      @documentVersionKey uniqueidentifier,
      @accessKey uniqueidentifier,
      @hierarchyKey uniqueidentifier,
      @parentFolderDepth int,
      @parentFolderSortOrder int,
      @tmp int

   -- default accesskey to everyone full control
   SELECT @accessKey = Convert(uniqueidentifier,ParameterValue)
     FROM SystemConfig
    WHERE ParameterName = 'Security.Token.EveryoneFullControlAccessKey'
      AND OrganizationKey = @organizationKey

   SET @ptr = CHARINDEX(N'/', @documentPath)
   IF @ptr > 0
   BEGIN
      --First segment contains the root name, the rest is the path
      SET @documentRootName = SUBSTRING(@documentPath, 1, @ptr - 1)
      SET @documentPath = SUBSTRING(@documentPath, @ptr + 1, LEN(@documentPath) - @ptr)
   END
   ELSE
   BEGIN
      --No root specified so use absolute root name
      SET @documentRootName = N'$'
      IF @documentPath = N'$'
         SET @documentPath = ''
   END
   -- check to make sure the root exists and the user has rights to it
   IF @rootHierarchyKey IS NULL
   BEGIN
       SELECT @rootHierarchyKey = RootHierarchyKey
         FROM HierarchyRoot
        WHERE HierarchyRootName = @documentRootName
          AND HierarchyRoot.OrganizationKey = @organizationKey
          AND EXISTS(
              SELECT 1
                FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
               WHERE AccessItem.AccessKey = HierarchyRoot.AccessKey
                 AND UserToken.UserKey = @userKey
                 AND (AccessItem.Permission&3)>0)
   END
   ELSE
   BEGIN
       SELECT @rootHierarchyKey = RootHierarchyKey
         FROM HierarchyRoot
        WHERE RootHierarchyKey = @rootHierarchyKey
          AND HierarchyRoot.OrganizationKey = @organizationKey
          AND EXISTS(
              SELECT 1
                FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
               WHERE AccessItem.AccessKey = HierarchyRoot.AccessKey
                 AND UserToken.UserKey = @userKey
                 AND (AccessItem.Permission&3)>0)
   END
   -- if we can get to the root, lets go inside the path
   IF @rootHierarchyKey IS NOT NULL
   BEGIN
      SET @hierarchyKey = @rootHierarchyKey
      SET @parent = @rootHierarchyKey
      SET @parentFolderDepth = 0
      SET @parentFolderSortOrder = 0

      -- loop thru the path components
      WHILE LEN(@documentPath) > 0
      BEGIN
         -- separate the next part of the path from the rest
         SET @ptr = CHARINDEX(N'/', @documentPath)
         IF @ptr > 0
         BEGIN
            SET @documentName = SUBSTRING(@documentPath, 1, @ptr - 1)
            SET @documentPath = SUBSTRING(@documentPath, @ptr + 1, LEN(@documentPath) - @ptr)
         END
         ELSE
         BEGIN
            SET @documentName = @documentPath
            SET @documentPath = N''
         END

         -- if there is still path parts left, documentName contains a folder name. Get its HierarchyKey
         IF LEN(@documentPath) > 0
         BEGIN
            SET @documentKey = null

            SELECT @parent = Hierarchy.ParentHierarchyKey,
                   @hierarchyKey = Hierarchy.HierarchyKey,
                   @parentFolderDepth = Hierarchy.Depth,
                   @parentFolderSortOrder = Hierarchy.SortOrder,
                   @documentKey = DocumentMain.DocumentKey,
                   @accessKey = DocumentMain.AccessKey
              FROM Hierarchy INNER JOIN DocumentMain ON Hierarchy.UniformKey = DocumentMain.DocumentVersionKey AND DocumentMain.DocumentStatusCode IN (10,20,30,40,60)
             WHERE Hierarchy.RootHierarchyKey = @rootHierarchyKey
               AND Hierarchy.ParentHierarchyKey = @parent
               AND DocumentMain.DocumentName = @documentName
            IF @@ROWCOUNT = 0
            -- its not there.  Since we had control permission at the higher level, create it.
            BEGIN
               SET @documentKey = NewID()
               SET @documentVersionKey = NewID()

               -- create the UniformRegistry entry first
               INSERT INTO UniformRegistry (UniformKey, ComponentKey)
               SELECT @documentKey, ComponentKey
                 FROM ComponentRegistry
                WHERE Name = 'Document'
                  AND InterfaceName = 'BusinessController'

               -- then the UniformRegistry entry for the version key
               INSERT INTO UniformRegistry (UniformKey, ComponentKey)
               SELECT @documentVersionKey, ComponentKey
                 FROM ComponentRegistry
                WHERE Name = 'DocumentVersion'
                  AND InterfaceName = 'BusinessController'

               -- then create the folder document
               INSERT INTO DocumentMain (
                      DocumentKey,
                      DocumentTypeCode,
                      DocumentName,
                      DocumentVersionKey,
                      DocumentStatusCode,
                      AlternateName,
                      IsSystem,
                      AccessKey,
                      ContainsChildrenFlag,
                      StatusUpdatedByUserKey,
                      StatusUpdatedOn,
                      UpdatedByUserKey,
                      UpdatedOn,
                      CreatedByUserKey,
                      CreatedOn)
               VALUES (
                      @documentKey,
                      'FOL',
                      @documentName,
                      @documentVersionKey,
                      40,
                      @documentName,
                      0,
                      @accessKey,
                      0,
                      @userKey,
                      GetDate(),
                      @userKey,
                      GetDate(),
                      @userKey,
                      GetDate())

              -- then create the hierarchy element
              SET @hierarchyKey = NewID()
        
              EXEC asi_HierarchyGetFolderSortOut @parent, @rootHierarchyKey, @parentFolderSortOrder, @parentFolderDepth, @documentName, @newSort OUTPUT

              INSERT INTO Hierarchy (HierarchyKey, RootHierarchyKey, ParentHierarchyKey, SortOrder, Depth, UniformType, UniformKey, IsChildAMemberOfParent)
              SELECT @hierarchyKey, RootHierarchyKey, HierarchyKey, @newSort, Depth + 1, UniformType, @documentVersionKey, IsChildAMemberOfParent
                FROM Hierarchy
               WHERE HierarchyKey = @parent

               SET @parent = @hierarchyKey
               SET @parentFolderSortOrder = @newSort
               SET @parentFolderDepth = @parentFolderDepth + 1
            END
            -- it is there, check the security
            ELSE
            BEGIN
               SELECT @tmp = count(*)
                 FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
                WHERE AccessItem.AccessKey = @accessKey
                  AND UserToken.UserKey = @userKey
                  AND (AccessItem.Permission&3)>0
               IF @tmp > 0 AND @hierarchyKey IS NOT NULL
               BEGIN
                  SET @parent = @hierarchyKey
               END
               ELSE
               BEGIN
                  SET @parent = NULL
                  SET @documentPath = NULL
                  SET @hierarchyKey = NULL
               END
            END
         END
      END -- WHILE LEN(@documentPath) > 0
      SELECT @hierarchyKey
   END -- IF @rootHierarchyKey IS NOT NULL
END


GO